package vn.fpt.fsoft.autotest.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TrustDistrictDB {
private static final String CLASS_NAME = "org.hsqldb.jdbcDriver";
    
    // URL, user name, password for connection
    private static final String URL = "jdbc:hsqldb:mem:testdb";
    private static final String USER_NAME = "sa";
    private static final String PASSWORD = "";

    private static Statement stmt;
    
    // SQL strings to create table and insert sample data
    private static final String USER_CREATE = "CREATE TABLE User (UserID int PRIMARY KEY IDENTITY, Username VARCHAR(30) NOT NULL UNIQUE, PASSWORD VARCHAR(30) NOT NULL, Email VARCHAR(30) NOT NULL UNIQUE, ROLE INT NOT NULL, ISACTIVE BIT NOT NULL);";
    private static final String COUNTRY_CREATE = "CREATE TABLE Country (CountryID int PRIMARY KEY IDENTITY, CountryName varchar(45) NOT NULL);";
    private static final String COUNTY_CREATE = "CREATE TABLE County (CountyID int PRIMARY KEY IDENTITY, CountryID int REFERENCES Country(CountryID), CountyName varchar(45));";
    private static final String TRUSTREGION_CREATE = "CREATE TABLE Trustregion (TrustRegionID int PRIMARY KEY IDENTITY, Name varchar(45) NOT NULL UNIQUE,Description varchar(300) ,CountryID int REFERENCES Country(CountryID),IsActive bit(1));";
    private static final String TRUSTDISTRICT_CREATE = "CREATE TABLE Trustdistrict (TrustDistrictID int PRIMARY KEY IDENTITY, Name varchar(45) NOT NULL,Description varchar(300), TrustRegionID int REFERENCES TrustRegion(TrustRegionID),IsActive bit(1));";
    private static final String GOVOFFICE_CREATE = "CREATE TABLE Govofficeregion (GovOfficeRegionID int PRIMARY KEY IDENTITY, GovOfficeRegionName varchar(45) NOT NULL,CountyID int REFERENCES County(CountyID),CountryID int REFERENCES Country(CountryID), Description varchar(300), IsActive bit(1));";
    
    private static final String INSERT_COUNTRY1 = "INSERT INTO Country (CountryName) VALUES('Singapore');";
    private static final String INSERT_COUNTRY2 = "INSERT INTO Country (CountryName) VALUES('VietNam');";
    private static final String INSERT_TRUSTREGION1 = "INSERT INTO Trustregion (Name,Description,CountryID,IsActive) VALUES('Trust Region a','this is trust region a',0,true);";
    private static final String INSERT_TRUSTREGION2 = "INSERT INTO Trustregion (Name,Description,CountryID,IsActive) VALUES('Trust Region b','this is trust region b',1,true);";
    private static final String INSERT_TRUSTDISTRICT1 = "INSERT INTO Trustdistrict (Name,Description,TrustRegionID,IsActive) VALUES('District 0','ABC0',0,true);";
    private static final String INSERT_TRUSTDISTRICT2 = "INSERT INTO Trustdistrict (Name,Description,TrustRegionID,IsActive) VALUES('District 1','ABC1',0,true);";
    public static void CreateData(){
        try {
            
            // Load the database driver, get a connection to the database
            Class.forName(CLASS_NAME);
            Connection conn = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
            
            // Get a statement from the connection
            stmt = conn.createStatement();

            // Add SQL string to statement as batch
            stmt.addBatch("DROP TABLE user IF EXISTS;");
            stmt.addBatch("DROP TABLE Govofficeregion IF EXISTS;");
            stmt.addBatch("DROP TABLE TrustDistrict IF EXISTS;");
            stmt.addBatch("DROP TABLE TrustRegion IF EXISTS;");
            stmt.addBatch("DROP TABLE County IF EXISTS;");
            stmt.addBatch("DROP TABLE Country IF EXISTS;");
            stmt.addBatch(USER_CREATE);
            stmt.addBatch(COUNTRY_CREATE);
            stmt.addBatch(COUNTY_CREATE);
            stmt.addBatch(TRUSTREGION_CREATE);
            stmt.addBatch(TRUSTDISTRICT_CREATE);
            stmt.addBatch(GOVOFFICE_CREATE);
            
            stmt.addBatch(INSERT_COUNTRY1);
            stmt.addBatch(INSERT_COUNTRY2);
            stmt.addBatch(INSERT_TRUSTREGION1);
            stmt.addBatch(INSERT_TRUSTREGION2);
            stmt.addBatch(INSERT_TRUSTDISTRICT1);
            stmt.addBatch(INSERT_TRUSTDISTRICT2);
            // then execute the batch
            conn.setAutoCommit(false);
            @SuppressWarnings("unused")
            int[] updateCount = stmt.executeBatch();
            conn.commit();
            conn.setAutoCommit(true);
            
           /* // Execute a simple query to verify the data was inserted or not
            ResultSet rs = stmt.executeQuery("SELECT * FROM USER");
            
            
            while (rs.next()) {
                System.out.println(rs.getString("USERNAME"));
            }
            
            // Close the result set, statement and the connection
            rs.close();*/
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            System.out.println("SQL Exception:");
            se.printStackTrace();
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}
